--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

-- CALL uvse_ExistsOrCreate('ART_SFAM');

 DELETE FROM se_art_sfam;



 -- select * from uv_psous_familles
 -- select * from se_ART_SFAM
 -- select * from &erp.ART_SFAM
 --select * from se_art_sfam where fam_cod not in (select fam_cod from &erp.art_fam)

 INSERT INTO se_art_sfam
 (SELECT SubStr(id,1,2) fam_cod,
         SubStr(id,3,2) sfam_cod,
         0 enreg_id,
         NULL destana_cod,
         NULL postana_cod,
         NULL cptafam_cod,
         NULL nomen_cod,
         'N' enreg_s,
         libelle sfam_lib,
         LIBELLED1 sfam_lib2,
         NULL rebut_coef,
         NULL ponderationpx_coef
    FROM uv_psous_familles sf
    WHERE EXISTS (SELECT 1 FROM uv_pfamilles f WHERE f.ID=SubStr(sf.id,1,2)) );

 UPDATE se_art_sfam tf SET enreg_id=(select enreg_id FROM &erp.art_sfam f WHERE f.fam_cod=tf.fam_cod AND tf.sfam_cod=f.sfam_cod) ;
 UPDATE se_art_sfam tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL;



 CALL uvse_synchroverserp('enreg_id','ART_SFAM','O') ;

 commit;
 
 
select * from (
SELECT &outils.stragg(source) source,fam_cod, sfam_cod, enreg_id, destana_cod, postana_cod, cptafam_cod, nomen_cod, enreg_s, sfam_lib, sfam_lib2, rebut_coef, ponderationpx_coef
FROM (
  SELECT 'uv' source ,g.* FROM se_art_sfam g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.art_sfam g
  )
GROUP BY fam_cod, sfam_cod, enreg_id, destana_cod, postana_cod, cptafam_cod, nomen_cod, enreg_s, sfam_lib, sfam_lib2, rebut_coef, ponderationpx_coef
HAVING Count(1)!=2) r
ORDER BY fam_cod,sfam_cod;